/*****************************************************************************
  Copyright ?2002-2004 by Martin Cook. All rights are reserved. If you like
  this code then feel free to go ahead and use it. The only thing I ask is 
  that you don't remove or alter my copyright notice. Your use of this 
  software is entirely at your own risk. I make no claims about the 
  reliability or fitness of this code for any particular purpose. If you 
  make changes or additions to this code then please clearly mark your code 
  as yours. If you have questions or comments then please contact me at: 
  martin@codegator.com
  
  Have Fun! :o)
*****************************************************************************/


using System;
using System.Data;
using System.Data.OleDb;
using System.Configuration;

using Microsoft.ApplicationBlocks.Data.OleDb;

using CG.Security.Configuration;
using CG.Security.Data.Configuration;

namespace CG.Security.Data.Access
{
	
	/// <summary>
	/// An Access specific implementation of IUserRoleData.
	/// </summary>
	internal sealed class UserRoleData : IUserRoleData
	{

		// *******************************************************************
		// Attributes.
		// *******************************************************************

		#region Attributes.

		/// <summary>
		/// The SQL to create a new user role.
		/// </summary>
		private static readonly string SQL_CREATE = 
			"INSERT INTO cg_security_user_role (user_id, role_id) " +
			"VALUES(@user_id, @role_id)";

		/// <summary>
		/// The SQL to delete a user role.
		/// </summary>
		private static readonly string SQL_DELETE = 
			"DELETE FROM cg_security_user_role " +
			"WHERE user_id = @user_id AND role_id = @role_id";

		/// <summary>
		/// The SQL to find a list of roles by user.
		/// </summary>
		private static readonly string SQL_FIND_BY_USER = 
			"SELECT a.user_id, a.role_id, b.role_name, b.role_description " +
			"FROM cg_security_user_role a, cg_security_role b " +
			"WHERE b.role_id = a.role_id AND a.user_id = @user_id " +
			"ORDER BY b.role_name";

		/// <summary>
		/// The SQL to find a list of users by role.
		/// </summary>
		private static readonly string SQL_FIND_BY_ROLE = 
			"SELECT a.user_id, a.role_id, b.user_name, b.create_date, b.last_login " +
			"FROM cg_security_user_role a, cg_security_user b " +
			"WHERE b.user_id = a.user_id AND a.role_id = @role_id " +
			"ORDER BY b.user_name";

		/// <summary>
		/// Various parameter names.
		/// </summary>
		private static readonly string PARM_USER_ID = "@user_id";
		private static readonly string PARM_ROLE_ID = "@role_id";

		#endregion
		
		// ******************************************************************
		// IUserRoleData implementation.
		// ******************************************************************

		#region IUserRoleData implementation.

		/// <summary>
		/// Creates a new association between a user and a role.
		/// </summary>
		/// <param name="userID">The identifier for the user.</param>
		/// <param name="roleID">The identifier for the role.</param>
		public void Create(
			int userID,
			int roleID
			)
		{

			// Read the runtime setup.
			DataSettings settings = SettingsManager.DataSettings;

			// Attempt to load the parameters.
			OleDbParameter[] parms = OleDbHelperParameterCache.GetCachedParameterSet(
				settings.ConnectionString,
				SQL_CREATE
				);

			// Did we fail?
			if (parms == null)
			{

				// Create the parameters.
				parms = new OleDbParameter[] 
				{
					new OleDbParameter(PARM_USER_ID, OleDbType.SmallInt),
					new OleDbParameter(PARM_ROLE_ID, OleDbType.SmallInt)
				};

				// Store the parameters in the cache.
				OleDbHelperParameterCache.CacheParameterSet(
					settings.ConnectionString, 
					SQL_CREATE, 
					parms
					);

			} // End if we failed to load the parameters.

			// Assign values to the parameters.
			parms[0].Value = userID;
			parms[1].Value = roleID;

			// Execute the SQL statement.
			OleDbHelper.ExecuteNonQuery(
				settings.ConnectionString, 
				CommandType.Text, 
				SQL_CREATE, 
				parms
				);

		} // End Create()

		// ******************************************************************

		/// <summary>
		/// Deletes an association between a user and a role.
		/// </summary>
		/// <param name="userID">The identifier for the user.</param>
		/// <param name="roleID">The identifier for the role.</param>
		public void Delete(
			int userID,
			int roleID
			)
		{

			// Read the runtime setup.
			DataSettings settings = SettingsManager.DataSettings;

			// Attempt to load the parameters.
			OleDbParameter[] parms = OleDbHelperParameterCache.GetCachedParameterSet(
				settings.ConnectionString,
				SQL_DELETE
				);

			// Did we fail?
			if (parms == null)
			{

				// Create the parameters.
				parms = new OleDbParameter[] 
				{
					new OleDbParameter(PARM_USER_ID, OleDbType.SmallInt),
					new OleDbParameter(PARM_ROLE_ID, OleDbType.SmallInt)
				};

				// Store the parameters in the cache.
				OleDbHelperParameterCache.CacheParameterSet(
					settings.ConnectionString, 
					SQL_DELETE, 
					parms
					);

			} // End if we failed to load the parameters.

			// Assign values to the parameters.
			parms[0].Value = userID;
			parms[1].Value = roleID;

			// Execute the SQL statement.
			OleDbHelper.ExecuteNonQuery(
				settings.ConnectionString,
				CommandType.Text, 
				SQL_DELETE, 
				parms
				);

		} // End Delete()

		// ******************************************************************

		/// <summary>
		/// Returns a list of all the users associated with the specified role.
		/// </summary>
		/// <param name="userID">The identifier for the user.</param>
		/// <returns>A DataSet containing all the users associated with the 
		/// specified role.</returns>
		public System.Data.DataSet FindByUser(
			int userID
			)
		{

			// Read the runtime setup.
			DataSettings settings = SettingsManager.DataSettings;

			// Attempt to load the parameters.
			OleDbParameter[] parms = OleDbHelperParameterCache.GetCachedParameterSet(
				settings.ConnectionString,
				SQL_FIND_BY_USER
				);

			// Did we fail?
			if (parms == null)
			{

				// Create the parameters.
				parms = new OleDbParameter[] 
				{
					new OleDbParameter(PARM_USER_ID, OleDbType.SmallInt)
				};

				// Store the parameters in the cache.
				OleDbHelperParameterCache.CacheParameterSet(
					settings.ConnectionString, 
					SQL_FIND_BY_USER, 
					parms
					);

			} // End if we failed to load the parameters.

			// Assign values to the parameters.
			parms[0].Value = userID;

			// Execute the SQL statement.
			return OleDbHelper.ExecuteDataset(
				settings.ConnectionString,
				CommandType.Text, 
				SQL_FIND_BY_USER, 
				parms
				);

		} // End FindByUser()

		// ******************************************************************

		/// <summary>
		/// Returns a list of all the roles associated with the specified user.
		/// </summary>
		/// <param name="roleID">The identifier for the role.</param>
		/// <returns>A DataSet containing all the roles associated with the 
		/// specified user.</returns>
		public System.Data.DataSet FindByRole(
			int roleID
			)
		{

			// Read the runtime setup.
			DataSettings settings = SettingsManager.DataSettings;

			// Attempt to load the parameters.
			OleDbParameter[] parms = OleDbHelperParameterCache.GetCachedParameterSet(
				settings.ConnectionString,
				SQL_FIND_BY_ROLE
				);

			// Did we fail?
			if (parms == null)
			{

				// Create the parameters.
				parms = new OleDbParameter[] 
				{
					new OleDbParameter(PARM_ROLE_ID, OleDbType.SmallInt)
				};

				// Store the parameters in the cache.
				OleDbHelperParameterCache.CacheParameterSet(
					settings.ConnectionString, 
					SQL_FIND_BY_ROLE, 
					parms
					);

			} // End if we failed to load the parameters.

			// Assign values to the parameters.
			parms[0].Value = roleID;

			// Execute the SQL statement.
			return OleDbHelper.ExecuteDataset(
				settings.ConnectionString,
				CommandType.Text, 
				SQL_FIND_BY_ROLE, 
				parms
				);

		} // End FindByRole()

		#endregion 

	} // End class UserRoleData

} // End namespace CG.Security.Data.Access

